﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Runtime.Serialization;

namespace WCF_LIBRARY_ATM
{

    public class DatabaseConnect
    {
            #region ConnectionString
            //private static String connectionString;      
            public static String ConnectionString
            {
                get
                {
                    return @"Data Source=MRPHONG-LAPTOP\MRPHONG;Initial Catalog=ATM;Integrated Security=True";
                    //return ConfigurationManager.ConnectionStrings["QLBVCB"].ConnectionString;
                   // return ReadConnectionString("input.xml");
                    //return @"Data Source=localhost;Initial Catalog=QLBVCB;Integrated Security=True";
                }
            }
            public static String ReadConnectionString(string file)
            {
                try
                {
                    XmlDocument doc = new XmlDocument();
                    doc.Load(file);
                    XmlElement root = doc.DocumentElement;
                    String connectionString = root.InnerText;
                    return connectionString;
                }
                catch (Exception ex)
                {
                    throw ex;
                }

            }
            #endregion
            #region ExecuteQuery
            public static DataTable ExecuteQuery(String query)
            {

                DataTable table = new DataTable();
                try
                {
                    SqlConnection connect = new SqlConnection(ConnectionString);
                    connect.Open();
                    try
                    {
                        SqlCommand command = connect.CreateCommand();
                        command.CommandType = CommandType.Text;
                        command.CommandText = query;
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = command;
                        adapter.Fill(table);
                    }

                    catch (SqlException ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        connect.Close();
                    }
                }

                catch (Exception ex)
                {
                    throw ex;
                }
                return table;
            }
            #endregion
            #region ExecuteNoneQuery
            public static int ExecuteNoneQuery(String query)
            {
                int n;
                try
                {

                    SqlConnection connect = new SqlConnection(ConnectionString);
                    connect.Open();
                    try
                    {
                        SqlCommand command = connect.CreateCommand();
                        command.CommandType = CommandType.Text;
                        command.CommandText = query;
                        n = command.ExecuteNonQuery();
                    }

                    catch (SqlException ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        connect.Close();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                return n;
            }
            #endregion

            #region transaction


            public static List<DataTable> executeTransaction(String[] queryArr, bool[] isNoneQueryArr)
            {
                List<DataTable> result = new List<DataTable>();
                SqlConnection connect = new SqlConnection(ConnectionString);
                connect.Open();

                SqlTransaction trans = connect.BeginTransaction();
                SqlCommand com = new SqlCommand();
                com = connect.CreateCommand();
                com.Transaction = trans;
                try
                {
                    for (int i = 0; i < queryArr.Length; i++)
                    {
                        if (isNoneQueryArr[i])
                        {
                            com.CommandType = CommandType.Text;
                            com.CommandText = queryArr[i];
                            com.ExecuteNonQuery();
                        }
                        else
                        {
                            com.CommandType = CommandType.Text;
                            com.CommandText = queryArr[i];
                            SqlDataAdapter adapter = new SqlDataAdapter();
                            adapter.SelectCommand = com;
                            DataTable table = new DataTable();
                            adapter.Fill(table);
                            result.Add(table);
                        }
                    }
                    trans.Commit();
                    connect.Close();
                    return result;
                }
                catch (Exception e)
                {
                    trans.Rollback();
                    throw e;
                }
            }
            #endregion

            public static void executeStoreProcedure(string procName, SqlParameter[] sqlParam)
            {

                SqlConnection connect = new SqlConnection(ConnectionString);
                try
                {
                    SqlCommand com = new SqlCommand();
                    com.CommandTimeout = 2000;
                    connect.Open();
                    com.Connection = connect;
                    com.CommandType = CommandType.StoredProcedure;
                    com.CommandText = procName;
                    for (int i = 0; i < sqlParam.Length; i++)
                    {
                        com.Parameters.Add(sqlParam[i]);
                    }
                    com.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw e;
                }

                finally
                {
                    connect.Close();
                }
            }

            public static List<DataTable> ThucThiStoreTraVeBang(string procName, SqlParameter[] sqlParam)
            {
                List<DataTable> result = new List<DataTable>();
                SqlConnection connect = new SqlConnection(ConnectionString);
                try
                {
                    SqlCommand com = new SqlCommand();
                    com.CommandTimeout = 2000;
                    connect.Open();
                    com.Connection = connect;
                    com.CommandType = CommandType.StoredProcedure;
                    com.CommandText = procName;
                    for (int i = 0; i < sqlParam.Length; i++)
                    {
                        com.Parameters.Add(sqlParam[i]);
                    }
                    SqlDataAdapter sqlDA = new SqlDataAdapter();
                    sqlDA.SelectCommand = com;
                    DataTable table1 = new DataTable();
                    DataTable table2 = new DataTable();
                    result.Add(table1);
                    result.Add(table2);
                    connect.Close();
                    return result;
                }
                catch (SqlException ex)
                {
                    connect.Close();
                    throw ex;
                }
            }
        }
    }


    
    

